package org.eclipse.epsilon.emc.lom;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;



import javax.swing.JOptionPane;

import org.eclipse.emf.common.util.EList;
import org.eclipse.emf.ecore.EAttribute;
import org.eclipse.emf.ecore.EClass;
import org.eclipse.emf.ecore.EObject;
import org.eclipse.emf.ecore.EReference;
import org.eclipse.emf.ecore.resource.Resource;


public class LiveObjectModelBuilder {
	
	
	protected String dbNamePath;
	private Connection con = null;
	private Statement stmt = null;
	private PreparedStatement pstmt = null;
	
	public LiveObjectModelBuilder(String _dbNamePath) {
		
		dbNamePath = _dbNamePath; 
		
	}
	
	public void createDatabase() {
			
		
		String sqlString1 = 
			"CREATE TABLE Objects (objectID INTEGER PRIMARY KEY AUTO_INCREMENT, eClassName VARCHAR(50))";
		String sqlString2 = 
			"CREATE TABLE Attributes (objectID INTEGER, attributeName VARCHAR(50), attributeValue VARCHAR(50))";
		String sqlString3 = 
			"CREATE TABLE References (objectID INTEGER , referenceName VARCHAR(50), valueID INTEGER)";
		
		String sqlString4 = 
				"DROP TABLE IF EXISTS Objects";
		
		String sqlString5 = 
			    "DROP TABLE IF EXISTS Attributes";
		
		String sqlString6 = 
			    "DROP TABLE IF EXISTS References";
		
		createTable(dbNamePath, sqlString1, sqlString4);
		createTable(dbNamePath, sqlString2, sqlString5);
		createTable(dbNamePath, sqlString3, sqlString6);
		closeConnection();
		
	}
	
	public void addResource(Resource resource) {
		
		//count of the last record in the table Objects - its 0 if table is empty
		int i = retreiveObjectId();
		
		
		Iterator itx = resource.getAllContents();
		
		HashMap<Object, Integer> objectCache = new HashMap<Object, Integer>();
		
		//Create hash map with correct ids
		int key = retreiveObjectId();
		
		while (itx.hasNext()) {
			
			key = key + 1;
			
			//put the EObjects in the hash map
			EObject object3 = (EObject) itx.next();
			objectCache.put(object3, key);
			
			//System.out.println(objectCache.get(object3));
			
		}
		
		Iterator allContentsIterator = resource.getAllContents();
		
		while (allContentsIterator.hasNext()) {
			
			EObject object = (EObject) allContentsIterator.next();
			
			//EObject object = resource.getContents().get(0);
			//Probably a for loop in order to get all the Eclasses of the model
			
			EClass eClass = object.eClass();
			System.out.println(eClass.getName());
			
			//insert EClassName into table Objects
			populateTableObjects(dbNamePath, eClass.getName());
			
			//keeps the object id
			i = i + 1;
			
			for(Iterator iter = eClass.getEAllAttributes().iterator(); iter.hasNext();){
				EAttribute attribute = (EAttribute)iter.next();
				Object value = object.eGet(attribute);
				//System.out.print("  " + attribute.getName() + ":" + value);
				
				if (attribute.isMany()){
					
					
					EList list2 = (EList) value;
					
					for(Object attrValue: list2){
						
						//transform all values of attributes to go in the database into string
						String value1 = value.toString();
						
						if(object.eIsSet(attribute)){
							//insert attributes into table attributes
							populateTableAttributes(dbNamePath, i, attribute.getName(), value1);
							//System.out.println();
						}else{
							//System.out.println("(default)");
							populateTableAttributes(dbNamePath, i, attribute.getName(), value1 + "(default)");
						}
					}
				}else{
					
					//transform all values of attributes to go in the database into string
					String value1 = value.toString();
					
					if(object.eIsSet(attribute)){
						
						//insert attributes into table attributes
						populateTableAttributes(dbNamePath, i, attribute.getName(), value1);
						//System.out.println();
					}else{
						//System.out.println("(default)");
						//populateTableAttributes(dbNamePath, i, attribute.getName(), value1 + "(default)");
					}
				}
			}
			
			for(Iterator it2 = eClass.getEAllReferences().iterator(); it2.hasNext();){
				
				EReference reference = (EReference)it2.next();
				Object value2 =  object.eGet(reference);
				
				if (reference.isMany()) {
					
					EList list = (EList) value2;
					//System.out.println(Integer.toString(list.size()));
					for (Object refValue : list) {
						
						//get object id from hashmap
						int valueId = objectCache.get(refValue);
						
						populateTableReferences(dbNamePath, i, reference.getName(), valueId);
						
					}
				}else{
					
					int valueId = objectCache.get(value2);
					populateTableReferences(dbNamePath, i, reference.getName(), valueId);
				}
				
				
				//System.out.println("  " + reference.getName() + ":" + value2);
			}
		}
		closeConnection();
	}
	
	private void openConnection(String dbNamePath){
		
		try {
			//load software driver
			Class.forName("org.h2.Driver");
	 
			//open connection
			con = DriverManager.getConnection("jdbc:h2:" + dbNamePath, "sa", "");
			//con = DriverManager.getConnection("jdbc:h2:E:/Database/test", "sa", "");
			
			
		} catch (Exception e) { 
			e.printStackTrace(); 
		}
	}
	
	private void closeConnection(){

		try {
			//close connection to db
			con.close();
	 
			//set con to null
			con = null;
			
		} catch (Exception e) { 
			e.printStackTrace(); 
		}
	}
	
	 private void createTable(String dbNamePath, String sqlString1, String sqlString2) {                                                
	    	
	    	//open connection
		 if(con == null){
			openConnection(dbNamePath);
		 }
			
			try {
				//instantiate statement
				stmt = con.createStatement();
				
				//try to execute sql
				stmt.executeUpdate(sqlString2);
				
				//try to execute sql
				stmt.executeUpdate(sqlString1);
				
				//inform user of success
				//JOptionPane.showMessageDialog( null, "Table created" );
				
				//close statement object
				stmt.close();
				
			} catch(SQLException s) {
				JOptionPane.showMessageDialog(null, s.getMessage(), "CREATE Error", JOptionPane.ERROR_MESSAGE);
				
		
			} catch(Exception e){
				//inform user of error
				JOptionPane.showMessageDialog(null, "General error");
					
			} finally {
				//close the connection
				//closeConnection();
			}
	    }
	 
	 private void populateTableObjects(String dbNamePath, String eClassName) {                                                  
	    	
	    	
		 	
	    	int rowsEffected = -1;
	    	
	    	//open connection
	    	if(con == null){
				openConnection(dbNamePath);
	    	}
	    	
			String sqlString = "INSERT INTO Objects (eClassName) VALUES (?) ";
				//+ "('" + eClassName + "')" ;
			
			
			try {
				
				//instantiate statement
				//stmt = con.createStatement();			
				pstmt = con.prepareStatement(sqlString);
				
				//set the ? in the prepared statement
				pstmt.setString(1, eClassName);
				
				//execute insert sql
				rowsEffected = pstmt.executeUpdate();

		   		//close statement
				stmt.close();
							
			} catch(SQLException s) {
				//inform user of error
				JOptionPane.showMessageDialog(null, s.getMessage(), "INSERT Error", JOptionPane.ERROR_MESSAGE);
				
				
			} catch(Exception e){
				//inform user of error
				JOptionPane.showMessageDialog(null, e.getMessage(), "General error", JOptionPane.ERROR_MESSAGE);
				
			} finally {
				//close the connection
				//closeConnection();
			}
	 }


	 private void populateTableAttributes(String dbNamePath, int id, String attrName, String attrValue ) {                                                  
	    	
	    	
		 	
	    	int rowsEffected = -1;
	    	
	    	//open connection
	    	if(con == null){
				openConnection(dbNamePath);
	    	}
	    	
			String sqlString = "INSERT INTO Attributes VALUES (?, ?, ?) ";
				//+ "(" + id + "," + "'" + attrName + "' , '" + attrValue + "')" ;
			
			
			try {
				//instantiate statement
				//stmt = con.createStatement();
				pstmt = con.prepareStatement(sqlString);
				
				//set the ?s in the prepared statement
				pstmt.setInt(1, id);
				pstmt.setString(2, attrName);
				pstmt.setString(3, attrValue);
				
				//execute insert sql
				rowsEffected = pstmt.executeUpdate();

		   		//close statement
				stmt.close();
							
			} catch(SQLException s) {
				//inform user of error
				JOptionPane.showMessageDialog(null, s.getMessage(), "INSERT Error", JOptionPane.ERROR_MESSAGE);
				
				
			} catch(Exception e){
				//inform user of error
				JOptionPane.showMessageDialog(null, e.getMessage(), "General error", JOptionPane.ERROR_MESSAGE);
				
			} finally {
				//close the connection
				//closeConnection();
			}
	 }
	 
	 private void populateTableReferences(String dbNamePath, int id, String refName, int valueId ) {                                                  
	    	
	    	
		 	
	    	int rowsEffected = -1;
	    	
	    	//open connection
	    	if(con == null){
				openConnection(dbNamePath);
	    	}
	    	
			String sqlString = "INSERT INTO References VALUES (?, ?, ?) ";
				//+ "(" + id + "," + "'" + RefName + "' , '" + Integer.toString(valueId) + "')" ;
			
			
			try {
				//instantiate statement
				//stmt = con.createStatement();			
				pstmt = con.prepareStatement(sqlString);
				
				pstmt.setInt(1, id);
				pstmt.setString(2, refName);
				pstmt.setInt(3, valueId);
				
				//execute insert sql
				rowsEffected = pstmt.executeUpdate();

		   		//close statement
				stmt.close();
							
			} catch(SQLException s) {
				//inform user of error
				JOptionPane.showMessageDialog(null, s.getMessage(), "INSERT Error", JOptionPane.ERROR_MESSAGE);
				
				
			} catch(Exception e){
				//inform user of error
				JOptionPane.showMessageDialog(null, e.getMessage(), "General error", JOptionPane.ERROR_MESSAGE);
				
			} finally {
				//close the connection
				//closeConnection();
			}
	 }
	 
	 private int retreiveObjectId() {                                              
	   
	    	
	    	//open connection
			 if(con == null){
				openConnection(dbNamePath);
			 }
			 
			//sql to select data
			String sqlString= "SELECT COUNT(*) AS Number FROM Objects";
			
			int id = -1;
					
			try {
				//instantiate statement
				stmt = con.createStatement();			
				
				//declare and populate result set
				ResultSet rs = 	stmt.executeQuery(sqlString);
				
				//local variables
				
				
				while (rs.next()) {
					//get row value
					id = rs.getInt("Number");
				}
				
				//close statement
				stmt.close();
				
				
			} catch(SQLException s) {
				//inform user of error
				System.out.println("\n\nError in retrieving data:\n" + s.getMessage());
			
			} catch(Exception e){
				//inform user of error
				System.out.println("\n\nGeneral Error in retrieving data:\n" + e.getMessage());
				
				
			} finally {
				
				//close the connection
				closeConnection();
				
			}
			return id;
			 
	    }                                             
	 
	 	
	 
	 
}
